/*****************************************************
-- Client Status
*****************************************************/
IF  OBJECT_ID(N'sp_Get_ClientStatus') IS NOT NULL 
Drop Procedure sp_Get_ClientStatus
GO
Create Procedure sp_Get_ClientStatus
    @ClientID bigint
AS
Begin
	Select * From ClientStatus
	Where UpdateOn = (Select Max(UpdateOn) From ClientStatus Where ClientID = @ClientID)
	And ClientID = @ClientID
End
GO

IF  OBJECT_ID(N'sp_Get_ClientStatusHistroy') IS NOT NULL 
Drop Procedure sp_Get_ClientStatusHistroy
GO
Create Procedure sp_Get_ClientStatusHistroy
    @ClientID bigint
AS
Begin
	Select * From ClientStatus
	Where UpdateOn >= Year(GetDate()) + '/' + Month(GetDate()) + '/' + Day(GetDate())
	And ClientID = @ClientID
End
GO

IF  OBJECT_ID(N'sp_Insert_ClientStatus') IS NOT NULL 
Drop Procedure sp_Insert_ClientStatus
GO
Create Procedure sp_Insert_ClientStatus
    @ClientID bigint
   ,@Status varchar(20)
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert ClientStatus(ClientID,Status,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@ClientID,@Status,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
End
GO

IF  OBJECT_ID(N'sp_Update_ClientStatus') IS NOT NULL 
Drop Procedure sp_Update_ClientStatus
GO
Create Procedure sp_Update_ClientStatus
    @ID BIGINT
    ,@ClientID bigint
   ,@Status varchar(20)
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update ClientStatus
   Set ClientID = @ClientID
   ,Status = @Status
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_ClientStatus') IS NOT NULL 
Drop Procedure sp_Delete_ClientStatus
GO
Create Procedure sp_Delete_ClientStatus
    @ID bigint
AS
Begin
    Delete From ClientStatus
    Where ID = @ID
End
GO

/**********************************************************
-- Command
***********************************************************/
IF  OBJECT_ID(N'sp_Insert_Command_CommandQueue') IS NOT NULL 
Drop Procedure sp_Insert_Command_CommandQueue
GO
Create Procedure sp_Insert_Command_CommandQueue
    @ClientID bigint
   ,@CommandStatus varchar(20)
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert Command_CommandQueue(ClientID,CommandStatus,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@ClientID,@CommandStatus,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
End
GO

IF  OBJECT_ID(N'sp_Update_Command_CommandQueue') IS NOT NULL 
Drop Procedure sp_Update_Command_CommandQueue
GO
Create Procedure sp_Update_Command_CommandQueue
    @ID BIGINT
    ,@ClientID bigint
   ,@CommandStatus varchar(20)
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update Command_CommandQueue
   Set ClientID = @ClientID
   ,CommandStatus = @CommandStatus
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_Command_CommandQueue') IS NOT NULL 
Drop Procedure sp_Delete_Command_CommandQueue
GO
Create Procedure sp_Delete_Command_CommandQueue
    @ID bigint
AS
Begin
    Delete From Command_CommandQueue
    Where ID = @ID
End
GO

/******************************************
-- Day Plan
******************************************/
IF  OBJECT_ID(N'sp_Get_Plan_DayPlan') IS NOT NULL 
Drop Procedure sp_Get_Plan_DayPlan
GO
Create Procedure sp_Get_Plan_DayPlan
	@Date Date
	,@ClientID bigint
As
Begin
	Select StartTime, EndTime, AllowUsePC, DayPlan.UpdateOn, DayPlan.UpdateByName
	From Plan_PlanItem Item
	Join Plan_DayPlan DayPlan
	On Item.PlanID = DayPlan.ID
	And DayPlan.Date = @Date
	And DayPlan.ClientID = @ClientID
End
Go

IF  OBJECT_ID(N'sp_Insert_Plan_DayPlan') IS NOT NULL 
Drop Procedure sp_Insert_Plan_DayPlan
GO
Create Procedure sp_Insert_Plan_DayPlan
    @ClientID bigint
   ,@Date date
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert Plan_DayPlan(ClientID,Date,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@ClientID,@Date,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
End
GO

IF  OBJECT_ID(N'sp_Update_Plan_DayPlan') IS NOT NULL 
Drop Procedure sp_Update_Plan_DayPlan
GO
Create Procedure sp_Update_Plan_DayPlan
    @ID BIGINT
    ,@ClientID bigint
   ,@Date date
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update Plan_DayPlan
   Set ClientID = @ClientID
   ,Date = @Date
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_Plan_DayPlan') IS NOT NULL 
Drop Procedure sp_Delete_Plan_DayPlan
GO
Create Procedure sp_Delete_Plan_DayPlan
    @ID bigint
AS
Begin
    Delete From Plan_DayPlan
    Where ID = @ID
End
GO



/*************************************************************
-- User_Client
**************************************************************/
IF  OBJECT_ID(N'sp_Insert_User_Client') IS NOT NULL 
Drop Procedure sp_Insert_User_Client
GO
Create Procedure sp_Insert_User_Client
    @Name nvarchar(50)
   ,@Password varchar(128)
   ,@IP varchar(100)
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert User_Client(Name,Password,IP,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@Name,@Password,@IP,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
End
GO

IF  OBJECT_ID(N'sp_Update_User_Client') IS NOT NULL 
Drop Procedure sp_Update_User_Client
GO
Create Procedure sp_Update_User_Client
    @ID BIGINT
    ,@Name nvarchar(50)
   ,@Password varchar(128)
   ,@IP varchar(100)
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update User_Client
   Set Name = @Name
   ,Password = @Password
   ,IP = @IP
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_User_Client') IS NOT NULL 
Drop Procedure sp_Delete_User_Client
GO
Create Procedure sp_Delete_User_Client
    @ID bigint
AS
Begin
    Delete From User_Client
    Where ID = @ID
End
GO

/******************************************
-- Parent
*******************************************/
IF  OBJECT_ID(N'sp_Insert_User_Parent') IS NOT NULL 
Drop Procedure sp_Insert_User_Parent
GO
Create Procedure sp_Insert_User_Parent
    @UserName nvarchar(50)
   ,@Password varchar(24)
   ,@Telephone varchar(50)
   ,@City nvarchar(50)
   ,@Province nvarchar(50)
   ,@Email varchar(300)
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert User_Parent(UserName,Password,Telephone,City,Province,Email,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@UserName,@Password,@Telephone,@City,@Province,@Email,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
select @@Identity
End
GO

IF  OBJECT_ID(N'sp_Update_User_Parent') IS NOT NULL 
Drop Procedure sp_Update_User_Parent
GO
Create Procedure sp_Update_User_Parent
    @ID BIGINT
    ,@UserName nvarchar(50)
   ,@Password varchar(24)
   ,@Telephone varchar(50)
   ,@City nvarchar(50)
   ,@Province nvarchar(50)
   ,@Email varchar(300)
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update User_Parent
   Set UserName = @UserName
   ,Password = @Password
   ,Telephone = @Telephone
   ,City = @City
   ,Province = @Province
   ,Email = @Email
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_User_Parent') IS NOT NULL 
Drop Procedure sp_Delete_User_Parent
GO
Create Procedure sp_Delete_User_Parent
    @ID bigint
AS
Begin
    Delete From User_Parent
    Where ID = @ID
End
GO

/***********************************************
-- Parent Client
************************************************/
IF  OBJECT_ID(N'sp_Insert_User_ParentClients') IS NOT NULL 
Drop Procedure sp_Insert_User_ParentClients
GO
Create Procedure sp_Insert_User_ParentClients
    @ParentID bigint
   ,@ClientID bigint
   ,@CreateBy INT
   ,@CreateByName nvarchar(50)
AS
Begin
Insert User_ParentClients(ParentID,ClientID,CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName)
Values(@ParentID,@ClientID,GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName)
End
GO

IF  OBJECT_ID(N'sp_Update_User_ParentClients') IS NOT NULL 
Drop Procedure sp_Update_User_ParentClients
GO
Create Procedure sp_Update_User_ParentClients
    @ID BIGINT
    ,@ParentID bigint
   ,@ClientID bigint
   ,@UpdateBy INT
   ,@UpdateByName nvarchar(50)
AS
Begin
Update User_ParentClients
   Set ParentID = @ParentID
   ,ClientID = @ClientID
   ,UpdateBy=@UpdateBy
   ,UpdateByName = @UpdateByName
   Where ID = @ID
End
GO

IF  OBJECT_ID(N'sp_Delete_User_ParentClients') IS NOT NULL 
Drop Procedure sp_Delete_User_ParentClients
GO
Create Procedure sp_Delete_User_ParentClients
    @ID bigint
AS
Begin
    Delete From User_ParentClients
    Where ID = @ID
End
GO


